

*** Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."


***locals 
local F=100  
local yu=200  // this is the chosen range +/- corra for yields we will use


**********************************************************************
*********  IDS
**********************************************************************

replace LEI = "" if LEI=="."
replace LEI = "anonymous" if LEI==""  & market_type=="secondary" // includes clien
											 								
*This bidderid was aquired by 191 
replace bidderid = 191 if bidderid==20 
 

**********************************************************************
*********  MATURITY DATES AND COUPONS 
**********************************************************************

*fill in missing maturity
bys isin: egen a0 = max(Maturity)
replace Maturity = a0 if Maturity==. 
format Maturity %td
drop a0 


bys isin: egen a0 = max(coupon)
replace coupon = a0 if coupon==.
drop a0 
	
	*---> I checked that Coupon and coupon are the same (up to precision rounding errors)
	drop Coupon // is redundant 

	
*indicator to distinguish between bills/bonds - clean: 	
replace type="bill" if coupon==0

	*check if RBB is correct:
	*tab isin if type=="RBB"
	*--> those are the isins auctioned in the real bond auctions + the one security issued on 1991
	
*those bonds are RBBs	
replace type="RBB" if isin=="CA135087B949"
replace type="RBB" if isin=="CA135087G997"
replace type="RBB" if isin=="CA135087UL60"
replace type="RBB" if isin=="CA135087VS05"
replace type="RBB" if isin=="CA135087WV25"
replace type="RBB" if isin=="CA135087XQ21"
replace type="RBB" if isin=="CA135087YK42"
replace type="RBB" if isin=="CA135087ZH04"

*all other are bonds
replace type="bond" if coupon>0 & coupon!=. & type!="RBB"
 


**********************************************************************	
********** AUCTION DATA
**********************************************************************


*fill in AuctionDate into the secondary market 
bys date isin: egen a0 = max(AuctionDate) 
replace AuctionDate = a0 if AuctionDate==.
format AuctionDate %td
drop a0 

*basic analysis of the auction data
unique isin if market_type=="auction"
unique isin if market_type=="secondary"
	
*fill in the date for the auctions
replace date = AuctionDate if market_type=="auction"

*fill in missing issuesum
bys isin AuctionDate: egen a0 = max(issuesum) if market_type=="auction"
replace issuesum=a0 if market_type=="auction" & issuesum==.
drop a0 

replace issuesum = 0 if issuesum==. & market_type=="auction"
	*Info: those are either buy-back auctions where No_buyback=1 or switch auctions. 
	
*fill in quantity data for auctions 
replace IIROC_QUANTITY=BidAllottedAmtSUM if  market_type=="auction" 

*drop emtpy tendertypes becasue not used 
drop if TenderType=="" & market_type=="auction"  
	*they used to be submitted netpositions of banks that didn't bid
	*check that those are indeed empty: browse if  TenderType=="" & market_type=="auction" 
	
*fill in yields and quantity from auction data 
replace IIROC_YIELD = BidYield 										if market_type=="auction"
replace IIROC_QUANTITY = BidAllottedAmtSUM  						if market_type=="auction"
	
*execution time vs. date 
gen double test = dofc(exec_time) if market_type=="auction"
format test %td
count if date!=test & market_type=="auction"
browse market_type  exec_time TenderType date test if date!=test  & market_type=="auction" // those look like early bids 

*replace early bids by 12:00 on auction date --> this is only correct for Regular Bond auctions
tab auctiontype  if date!=test  & market_type=="auction" 
replace exec_time=dhms(AuctionDate, 11, 00, 0) if market_type=="auction" & date!=test
drop test
 
 
 
**********************************************************************
*********  DATES, SETTLEMENT AND TIME IN THE SECONDARY MARKET
**********************************************************************

	/* 
	Infos on settlement dates for T-Bills
	For regular bills the settlement date is wrong:  it should be auctiondate+2
	For CM bills the settlement date is right: t+0
	For OTC trades: they should be t+0, 
					except if the trade is after CDSX closes, in which case it should be t+1.	
	*/
	
	

*EXECTUION TIME ON WEEKENDS 
g dayofweek = dow(date)
label var dayofweek "day of the week (must be 1-5)"
tab dayofweek // few on non-week days 
	
*For sundays, I will manually change them to monday 7:00 
gen fake= hms(07,00,00)  
format fake %tcHH:MM:SS
gen double fake2 = (date+1) *24*60*60*1000 + fake 
replace exec_time =fake2 			if dayofweek==0
replace date = (date+1)  			if dayofweek==0
replace dayofweek = dow(date) 		if dayofweek==0
drop fake fake2 

*For Saturdays, I will manually change them to Friday 17:00 
gen fake= hms(17,00,00)  
format fake %tcHH:MM:SS
gen double fake2 = (date+1) *24*60*60*1000 + fake 
replace exec_time =fake2 			if dayofweek==6
replace date = (date+1)  			if dayofweek==6
replace dayofweek = dow(date) 		if dayofweek==6
drop fake fake2 


*EXECTUION TIME VS  DATE (sometimes rounding issues)
gen double test = dofc(exec_time) if market_type=="secondary"
format test %td
count if date!=test & market_type=="secondary"
drop test
	

*MATURITY VS SETTLEMENT AND DATES
count if Maturity < date & market_type=="secondary"
	
	*(a) correct date
	gen a0 = (Maturity < date) 
	replace date = Maturity if a0==1 & market_type=="secondary"

	*(b) correct time-stamp - I am keeping the same time but changing the date
	generate s  = string(exec_time,"%tc") // put execution time into string
	gen str ts1 =  substr(s, 11, 8)
	generate ts2  = string(date, "%td")
	gen strdate = ts2 + " " + ts1 
	gen double dt = clock(strdate, "DMY hms")
	format dt %tc
	replace exec_time = dt if a0==1 & market_type=="secondary"
	drop s ts1 ts2 dt strdate a0


*SETTLEMENT vs. DATES 
count if settlement_date<date & market_type=="secondary"
	
	/*
	*how bad are settlement-dates?
	gen settlement_date1 = date+1
	gen settlement_date2 = date+2
	gen settlement_date3 = date+3
	format settlement_date1 %td
	format settlement_date2 %td
	format settlement_date3 %td
	count if settlement_date==date // these are fine
	count if settlement_date == settlement_date1 // these could also be fine

	*these are ``fine" (allowing for a day delay in reporting)
	count if settlement_date==date & market_type=="secondary"
	count if settlement_date == settlement_date1  & market_type=="secondary"
	count if settlement_date == settlement_date3 & dayofweek==5  & market_type=="secondary" // on a Friday
	count if market_type=="secondary" 
	*/
	

*settlement dates to far into the future 
gen a1 = standardSettlementDate - date  
replace standardSettlementDate = date if a1>7
gen a0 = settlement_date - date  
replace settlement_date = standardSettlementDate if a0>7 
drop a0 a1 


*settlement date < date 	
replace  settlement_date  = standardSettlementDate  if  market_type=="secondary" & settlement_date<date  // can't be!
		
	/* INFO on Settlement convention: 
	*this varies over time and by country and by bond type.  So on this I just have to trust 
	that the people who generated the standard settlement variable know what they are doing. 
	Some is t+0, t+1, t+2, t+3, t+4.  
	But nothing more than t+4.  I guess holidays make a difference but I don't know these rules.
	*/
	
*maturity before settlement-date 
count if Maturity<settlement_date & market_type=="secondary"
replace settlement_date = Maturity if Maturity<settlement_date & market_type=="secondary"


**** DAYS UNTIL MATURITY
gen days_to_maturity   = Maturity - settlement_date
gen days_to_maturity_e = Maturity - date



************************************************************************
*** YIELDS AND PRICES IN THE SECONDARY MARKET
************************************************************************

*** FOR BILLS  (because price is better according to IIROC)
local F=100
gen double yield_org = IIROC_YIELD  
gen double  a0 = (`F' - IIROC_PRICE)/(IIROC_PRICE) * 365/days_to_maturity *100^2 if type=="bill" // in % 
replace  IIROC_YIELD = a0  if market_type=="secondary" & type=="bill"
drop a0


**********************************************************************
********* IIROC_TRADING_VENUE IN THE SECONDARY MARKET
**********************************************************************

	/*
	All the legal information on the firms.
 
	https://www.gleif.org/lei/213800U91OWU6L9YIT33
	https://www.gleif.org/lei/5493001KJTIIGC8Y1R12
	https://www.gleif.org/lei/5493008EYOZ1SZ0ZN858
	https://www.gleif.org/lei/5493002DMKJE7H5VGZ78
	*/

*correct IIROC_TRADING_VENUE_ID
gen trading_venue = ""
replace trading_venue = "CanDeal" 		if IIROC_TRADING_VENUE_ID == 4 
replace trading_venue = "Brokertec" 	if IIROC_TRADING_VENUE_ID == 3
replace trading_venue = "Bloomberg" 	if IIROC_TRADING_VENUE_ID == 1
replace trading_venue = "error" 		if IIROC_TRADING_VENUE_ID == 0

replace trading_venue = "parameter" 	if  IIROC_TRADING_VENUE_ID == 2
replace trading_venue = "IDBB" 			if  IIROC_TRADING_VENUE_ID == 196  ///
										|	IIROC_TRADING_VENUE_ID == 194  ///
										|	IIROC_TRADING_VENUE_ID == 200 

	count if trading_venue =="IDBB" & (LEI!="200" & LEI!="196" & LEI!="194")
	*--> fine


replace trading_venue ="OTC" if trading_venue=="" & market_type=="secondary"
tab trading_venue


**********************************************************************
********* OUTSTANDING AMOUNT - this is never used because incomplete
**********************************************************************
	
sort  isin settlement_date
browse exec_time date settlement_date isin settlement_date outstanding market_type AuctionDeadline coupon if isin=="CA135087A461"
sort  isin date
browse exec_time date settlement_date isin settlement_date outstanding market_type AuctionDeadline coupon if isin=="CA135087A461"

/*
	preserve
	collapse  coupon, by(isin date outstanding)
	drop if outstanding==.
	bys isin date: gen a0=_N
	tab a0
	browse if a0>1
		*---> uniuqe per date other than some 0's (that are weird)
	restore 
*/

	
*fill in oustanding amount per date 
bys isin date: egen a0 = max(outstanding)
replace outstanding = a0 
drop a0 	
	
*Correc the outstanding amount on auction dates 
preserve
collapse outstanding, by(isin date)

sort isin date
bys isin: gen  outstanding_before = outstanding[_n-1] 

tempfile temp
save  `temp', replace	
restore

merge m:1 isin date using `temp'
drop _merge

*Correct oustanding amount at the time of the auction 
replace outstanding = outstanding_before if exec_time<AuctionDeadline  & AuctionDeadline!=.
sort isin exec_time  
browse isin date exec_time AuctionDeadline market_type outstanding outstanding_before if  AuctionDeadline!=.




